The Azure SQL Edge demo is based on a Contoso Renewable Energy, a wind turbine farm that leverages Azure SQL Edge for data processing onboard the generator.

The demo will walk you through resolving an alert being raised due to wind turbulence being detected at the device. You will train a model and deploy it to SQL DB Edge that will correct the detected wind wake and ultimately optimize power output.

We will also look at some of the security features available with Azure SQL Edge.

The data stored in the database table represents the following:

• RecordId:Unique identifier for the entry.
• TurbineId:Unique identifier for the turbine in scope.
• GearboxOilLevel:Oil level recorded for the turbine gear box at the time of the reading.
• GearboxOilTemp:Oil temperature recorded for the turbine gear box at the time of the reading.
• GeneratorActivePower:Active Power recorded by the turbine generator.
• GeneratorSpeed:Speed recorded by the turbine generator.
• GeneratorTemp:Temperature recorded by the turbine generator.
• GeneratorTorque:Torque recorded by the turbine generator.
• GridFrequency:Frequency recorded in the grid for the specific wind turbine.
• GridVoltage:Voltage recorded in the grid for the specific wind turbine.
• HydraulicOilPressure:Current pressure of the hydraulic oil for the wind turbine.
• NacelleAngle:Angle of the nacelle at the time of the reading (the housing that contains all the generating components).
• PitchAngle:Pitch angle of the blades against the oncoming air stream to obtain the optimal amount of energy.
• Vibration:Vibration of the wind turbine at the time of the reading.
• WindSpeedAverage:Average wind speed calculated from the last X records.
• Precipitation:Flag to represent if rain was present at the time of the reading.
• WindTempAverage:Average wind temperature calculated from the last X records.
• OverallWindDirection:Overall wind direction recorded at the time of the reading.
• TurbineWindDirection:Turbine wind direction recorded at the time of the reading.
• TurbineSpeedAverage:Average turbine speed calculated from the last X records.
• WindSpeedStdDev:Standard Deviation of the last X WindSpeedAverage records.
• TurbineSpeedStdDev:Standard Deviation of the last X TurbineSpeedAverage records.

The above dataset definition contains trends that will enable us to detect the existence of wake in a wind turbine. There are two main conditions that influence the presence of wind wake:

1. Overall wind farm and turbine wind direction are both between 40° - 45° degrees.
2. TurbineSpeedStdDev and WindSpeedStdDev have been too far apart for greater than a minute.

The wind turbine will experience wake when the turbine wind direction is between 40° - 45° degrees and the values of TurbineSpeedStdDev and WindSpeedStdDev are not similar. For example:

• Wake Present:
  • TurbineWindDirection = 43.5°
  • TurbineSpeedStdDev = 8.231
  • WindSpeedStdDev = 0.23
• Wake Not Present:
  • TurbineWindDirection = 23.5°
  • TurbineSpeedStdDev = 0.921
  • WindSpeedStdDev = 0.213

An Azure Resource Manager (ARM) template will be used to deploy all the required resources in the solution. Click on the link below to start the deployment.

TODO ^^ Need to update the ARM location

Follow the steps to deploy the required Azure resources:

BASICS

• Subscription: Select the Subscription.
• Resource group: Click on 'Create new' and provide a unique name for the Resource Group
• Location: Select the Region where to deploy the resources. Keep in mind that all resources will be deployed to this region so make sure it supports all of the required services. The template has been confirmed to work in West US 2.

1. Read and accept the TERMS AND CONDITIONS by checking the box.
2. Click the Purchase button and wait for the deployment to finish.

Some resources require some extra configuration.

The Edge Module will require access the DACPAC package in order to setup the database.

1. In the Azure portal select the Resource Group you created earlier.
2. Select the Storage account resource from the list.
3. Click the Containers option in the left menu under Blob service.
4. Click the dacpac container.
5. Click the Upload button.
6. Click the Select a file input and select the file under the project folder: sql/turbine-sensor-db-dacpac.zip.
7. Click the Upload button.
8. Once the file is uploaded, click on it.
9. Click Generate SAS tab.
10. Update the Expiry year to 2050.
11. Click Generate SAS token and URL
12. Copy the value in Blob SAS URL and save it for later in the setup.

As security settings were deployed as part of the DACPAC package, below is a review of the security setup within the database.

1. Create users without a login for simpler testing:

   /* Create users using the logins created */CREATEUSEROperatorUser WITHOUT LOGIN; CREATEUSERDataScientistUser WITHOUT LOGIN; CREATEUSERSecurityUser WITHOUT LOGIN; CREATEUSERTurbineUser WITHOUT LOGIN;

2. Assigned permissions for each user:

   /* Grant permissions to users */GRANTSELECTON RealtimeSensorRecord TO OperatorUser; GRANTSELECTON RealtimeSensorRecord TO DataScientistUser; GRANTSELECTON RealtimeSensorRecord TO SecurityUser; GRANTSELECT, INSERT ON RealtimeSensorRecord TO TurbineUser;

   Note: All users can SELECT, however the TurbineUser can also INSERT to the table.

3. For privacy reasons, mask the last 4 digits of the SensorId for the Data Scientist user:

   /*Mask the last four digits of the serial number (Sensor ID) for the Data Scientist User*/ALTERTABLE RealtimeSensorRecord ALTER COLUMN SensorId varchar(50) MASKED WITH (FUNCTION ='partial(34,"XXXX",0)'); DENY UNMASK TO DataScientistUser; GO

4. Add a policy using a filter predicate and a function to manage access to data events:

   • We updated the SensorType column as it is required in our function then created a new schema to store it.

     /*** Operator: Can see all events* Data Scientist: Can see everything BUT Hatch Sensor events* Security: Can ONLY see Hatch Sensor events*/ALTERTABLE RealtimeSensorRecord ALTER COLUMN SensorType sysname GO CREATESCHEMASecurity; GO

   • Add the function that will ensure each query is authorized based on Sensor Type/User.

     /*** Operator: Can see all events* Data Scientist: Can see everything BUT Hatch Sensor events* Security: Can ONLY see Hatch Sensor events*/CREATEFUNCTIONSecurity.fn_securitypredicate(@SensorType AS sysname) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT1AS fn_securitypredicate_result WHERE USER_NAME() ='OperatorUser'OR USER_NAME() ='dbo'OR (USER_NAME() ='DataScientistUser'AND @SensorType <>'HatchSensor') OR (USER_NAME() ='SecurityUser'AND @SensorType ='HatchSensor');

   • Add a filter to to use the function.

     CREATE SECURITY POLICY SensorsDataFilter ADD FILTER PREDICATE Security.fn_securitypredicate(SensorType) ONdbo.RealtimeSensorRecord WITH (STATE =ON);

In this section, we will setup our notebook with the required files for the generation of the wind adapt model.

1. In the Azure portal select the Resource Group you created earlier.
2. Select the Storage account resource.
3. Click the Containers option in the left menu under Blob service.
4. Click the azureml-blobstore-GUID container.
5. Click the Upload button in the top.
6. Click the Select a file input and select the ml\data\TrainingDataset.parquet from your repo.
7. Click the Upload button and wait for the upload to finish.

1. Select Azure Active Directory option from the main navigation in the Azure Portal:

   

2. Copy the Tenant Id value from the overview as you will need this value later.

3. Go back to the Resource Group you created earlier.

4. Select the Machine Learning resource.

5. Take note of the following values to be used later in the deployment

   • Resource Group
   • Workspace Name
   • Subscription ID

6. Click the Launch now button to open the Machine Learning workspace.

7. Click the Notebooks option in the left menu under Author.

8. Click the Create new folder button at the top of the navigation panel.

9. Enter the name scripts for as the folder name and click the Create button.

10. Click the Upload files button at the top of the navigation panel.

11. Select the 2 files inside the ml\scripts folder:

    • ml\scripts\train.py
    • ml\scripts\utils.py

12. Select the newly created scripts folder from the target directory list.

13. Click the Upload button and wait for the upload to finish.

14. Click the Upload files button again.

15. Select the following 2 files inside the ml folder:

    • ml\utils.py
    • ml\wind-turbine-scikit.ipynb

    Note: The utils.py is a different file from the previous step.

16. Select your username folder from the target directory list.

17. Click the Upload button.

We need configure values within the notebook before being able to execute it:

1. Click the wind-turbine-scikit.ipynb in the My files navigation:

2. Click the New Compute button.

3. Enter the name compute-{your-initials}.

4. Select CPU (Central Processing Unit) from the Virtual machine type dropdown.

5. Select the virtual machine size Standard_D12_v2.

6. Click the Create button and wait for the compute to be created.

   Note: This process can take several minutes; wait until status of compute is Running.

7. Click the Edit dropdown and select the Edit in Jupyter option.

   Note: If required, login with your Azure credentials.

8. Replace the values within the Setup Azure ML cell with the values you obtained in the Notebook files upload section:

   interactive_auth = InteractiveLoginAuthentication(tenant_id="<tenant_id>") # Get instance of the Workspace and write it to config file ws = Workspace( subscription_id = '<subscription_id>', resource_group = '<resource_group>', workspace_name = '<workspace_name>', auth = interactive_auth) 

9. Click File > Save and Checkpoint from the menu.

10. Select the Install requirements cell and click Run from the menu, wait for the script to execute before continuing.

11. Select the Setup Azure ML cell and click Run from the menu.

    IMPORTANT: Observe the output to authenticate via the URL provided (https://microsoft.com/devicelogin).

12. From here, Run the remaining cells sequentially until you have executed the notebook.

    IMPORTANT: Remember to wait for each cell to execute before continuing.

13. Go back to the azure resource group and click the Storage Account resource.

14. Click the Containers option in the left menu.

15. Click the container in the list with a name like: azureml-blobstore-{guid}.

16. A new file with the name windturbinewake.model.onnx will be in the container.

17. Click the windturbinewake.model.onnx file

18. Click the Generate SAS tab option.

19. Change the Expiry Year to 2050.

20. Click the Generate SAS token and URL button and wait for the SAS to be generated.

21. Copy the Blob SAS URL value for later in the demo usage section.

    IMPORTANT: As this process does take some time, once you have saved your model to blob storage, you will not be required to execute this every time you run through the demo. Showing the notebook flow may be adequate for demo purposes. You will just need the blob SAS for the SQL DB Edge Demo Usage section later in the document.

In this section, we will set up an Edge device within our IoT Hub instance.

1. In the Azure portal select the Resource Group you created earlier.
2. Select the IoT Hub resource.
3. Click on IoT Edge from the left navigation.
4. Click + Add an IoT Edge Device.
5. Enter a Device ID and leave all other fields as default.
6. Click Save.
7. Once the device has been created, select the device and copy the Primary Connection String for later in this setup.

1. Click on the link below to start the deploy to Azure:

   

2. On the newly launched window, fill in the available form fields:

   • Subscription: Your subscription.
   • Resource group: Select the resource group you created earlier.
   • DNS Label Prefix: Your initials and birth year.
   • Admin Username: Enter microsoft as default.
   • Device Connection String: The device connection string that you got from previous section.
   • VM Size: The size of the virtual machine to be deployed.
   • Ubuntu OS Version: The version of the Ubuntu OS to be installed on the base virtual machine.
   • Location: The geographic region to deploy the virtual machine into, this value defaults to the location of the selected Resource Group.
   • Authentication Type: Choose the password option.
   • Admin Password or Key: Enter M1cr0s0ft2020.

3. Accept the Terms and Conditions.

4. Select Purchase to begin the deployment.

1. Once the deployment is complete, go back to the Resource Group you created earlier.

2. Select the Virtual Machine resource.

   Note: Take note of the machine name, this should be in the format vm-0000000000000. Also, take note of the associated DNS Name, which should be in the format <dnsLabelPrefix>.<location>.cloudapp.azure.com. The DNS Name can be obtained from the Overview section of the newly deployed virtual machine within the Azure portal.

3. If you want to SSH into this VM after setup, use the associated DNS Name with the command: ssh <adminUsername>@<DNS_Name>. You can use the password you created in the previous step.

   IMPORTANT: There is an optional section at the end of this document showing some example commands.

1. Install Visual Studio Code (VS Code).
2. Install Docker Community Edition (CE). Don't sign in to Docker Desktop after Docker CE is installed.
3. Install the following extensions for VS Code:
   • Azure Machine Learning (Azure Account will be automatically installed)
   • Azure IoT Hub Toolkit
   • Azure IoT Edge
   • Docker Extension
4. Restart VS Code.
5. Select [View > Command Palette…] to open the command palette box, then enter [Python: Select Interpreter] command in the command palette box to select your Python interpreter.
6. Enter [Azure: Sign In] command in the command palette box to sign in Azure account and select your subscription.

1. Launch Visual Studio Code, and select File > Open Workspace... command to open the edge\sensor-solution.code-workspace.

2. Update the .env file with the values for your container registry.

   • In the Azure portal select the Resource Group you created earlier.

   • Select the Container Registry resource.

   • Select Access Keys from the left navigation.

   • Update the following in edge/SensorSolution/.env with the following values from Access Keys within the Container Registry:

     CONTAINER_REGISTRY_NAME=<Login Server> (Ensure this is the login server and NOT the Registry Name)

     CONTAINER_REGISTRY_USER_NAME=<Username>

     CONTAINER_REGISTRY_PASSWORD=<Password>

     SQL_PACKAGE=<SQL Package Blob URL> (the one you obtained earlier in the setup)

   • Save the file.

3. Sign in to your Azure Container Registry by entering the following command in the Visual Studio Code integrated terminal (replace <REGISTRY_USER_NAME>, <REGISTRY_PASSWORD>, and <REGISTRY_NAME> with your container registry values set in the .env file IN THE PREVIOUS STEP).

   docker login -u <CONTAINER_REGISTRY_USER_NAME> -p <CONTAINER_REGISTRY_PASSWORD> <CONTAINER_REGISTRY_NAME>

   IMPORTANT: Ensure you have amd64 selected as the architecture in the bottom navigation bar of VS Code.

4. Right-click on edge/SensorSolution/deployment.debug.template.json and select the Build and Push IoT Edge Solution command to generate a new deployment.debug.amd64.json file in the config folder, build a module image, and push the image to the specified ACR repository.

   IMPORTANT: If you have amended code in your module, you will need to increment the version number in module.json so the new version will get deployed to the device in the next steps.

   Note: Some red warnings "/usr/bin/find: '/proc/XXX': No such file or directory" and "debconf: delaying package configuration, since apt-utils is not installed" displayed during the building process can be ignored.

5. Ensure you have the correct Iot Hub selected in VS Code.

   • In the Azure IoT Hub extension, click Select IoT Hub from the hamburger menu. (Alternatively, select Azure IoT Hub: Select IoT Hub from the Command Palette)
   • Select your Subscription.
   • Select the IoT Hub you created earlier in the setup.

6. Right-click config\deployment.debug.amd64.json and select Create Deployment for a Single Device.

7. Select the device you created earlier.

8. Wait for deployment to be completed.

Follow the next steps to setup the required module twin connection string property.

1. In the Azure portal select the Resource Group you created earlier.
2. Select the IoT Hub resource.
3. Click the IoT Edge option in the left menu under Automatic Device Management.
4. Click the device you created earlier.
5. Click the SensorModule from the modules list.
6. Copy the Connection string (primary key) value and save for the next step.
7. Go back to your Resource Group.
8. Select the App Service resource.
9. Click the Configuration option in the left menu.
10. Under the Application settings find the IoTHub:ModuleConnectionString and click it.
11. Paste the module connection string that you got before to the value input field.
12. Click the OK button.
13. Click the Save button on the top to apply the change.

Open the Web App.

1. In the Azure portal select the Resource Group you created earlier.
2. Select the App Service resource.
3. Click Browse to go the application on a desktop machine.

Investigate Turbine Issue

1. Click view on the alert. A query is ran against the SQL DB Edge instance.
2. Notice the Operator can't see the Security Alert due to as the permissions we set earlier.
3. You can notice a drop in the Power Generated chart.
4. Click the Environmental button.
5. You can notice the Wind Speed and Direction at the turbine is a lot more turbulent than the rest of the Wind Farm. This could indicate wind wake.

Now we need to run our notebook in order to generate the Onnx model that we will use to resolve the alert.

Important: As mentioned earlier in the document, you can choose to run through executing the notebook cells in the Notebook Setup section again to obtain the model. Or you can use your Blob URL you created during the initial setup.

Now we have our wind adapt model, lets update the module to correct the turbine.

1. Go back to the azure resource group and click the IoT Hub resource.
2. Click the Iot Edge option in the left menu.
3. Click the created device from previous steps.
4. Click the SensorModule from the modules list.
5. Click the Module Identity Twin option in the top menu.
6. Find the properties section in the json.
7. Find the desired section in the json.
8. Find the OnnxModelUrl property and update the value with the model Blob URL from the previous section.
9. Click the Save button.
10. Go back to the Web App.
11. You will notice a notification indicating the alert has been resolved.
12. Click on the Resfresh button.
13. Notice the turbine Wind Speed and Direction has stabilized.
14. If you go back to the dashboard view. You will notice Unit 34 no longer has an alert.

This steps allows you to restart the demo.

1. Go back to the azure resource group and click the IoT Hub resource.
2. Click the Iot Edge option in the left menu.
3. Click the created device from previous steps.
4. Click the SensorModule from the modules list.
5. Click the Module Identity Twin option in the top menu.
6. Find the properties section in the json.
7. Find the desired section in the json.
8. Find the OnnxModelUrl property set the value as empty.

Note: Since the Alert property value was already in start we don't need to updated it but the module will set the reported property with this value.

1. Click the Save button.
2. Go back to the Web App and refresh.
3. After a short time the alert will appear again.

This section describe steps that allow us to see extra features of the resources as a reference only.

Here we will see how to run commands into to the device virtual machine from the terminal using SSH connection.

1. In the Azure portal select the Resource Group you created earlier.

2. Select the Virtual machine resource.

3. Copy the DNS name to use it for the connection.

4. In a terminal run the following command replacing the DNS name: ssh microsoft@<DNS_Name>

   Note: The above command is assuming that you use the default Admin username when deploying the VM.

5. Enter the password to connect.

   Note: Default password is: M1cr0s0ft2020

6. Run the following command to see the list of modules running: sudo iotedge list

   Note: You can should be able to see the AzureSQLDatabaseEdge and SensorModule we deployed earlier.

7. Run the following command to see the logs of the Sensor Module: sudo iotedge logs SensorModule

   • Following we will connect with the edge sql server to run a simple query by doing:
     • Get the list of containers running with docker: sudo docker container list.
     • Get the container ID of the AzureSQLDatabaseEdge docker image running.
     • Connect to the container using the id that you got and the command: sudo docker exec -it CONTAINERID /bin/sh.
     • Connect to the container using: /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'Microsoft2020$'.
     • Connect to the database using: USE [turbine-sensor-db] and then go.
     • Query the number of records in the table using: select count(*) from RealtimeSensorRecord and go.

We've seen issues with different subscription types: MSDN, AIRS, etc... not being able to deploy certain resources to certain regions. We've found that deploying to West US 2 works consistently. If you have a deployment error, try deploying to West US 2. The resources inherit their deployment region from the Resource Group location.